package com.telecom.cms.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.util.SystemOutLogger;
import com.telecom.cms.forms.*;
import com.telecom.cms.util.Constant;
import com.telecom.cms.dto.*;

public class SEG_BIZ_GROUP_DEFDAO {
	private static List instlist = null;

	private static final String SQL_FINDBYID = "select * from "
			+ Constant.SEG_BIZ_GROUP_DEF + " where BOOKMARK_ID = ? order by 1";

	private static final String SQL_FINDID = "select * from "
			+ Constant.SEG_BIZ_GROUP_DEF + " where ATOM_GROUP_ID = ?";

	private static final String SQL_FINDALL = "select * from "
			+ Constant.SEG_BIZ_GROUP_DEF + " order by atom_group_id";

	private static final String SQL_INSERT = "Insert Into "
			+ Constant.SEG_BIZ_GROUP_DEF
			+ " (ATOM_GROUP_ID,ATOM_GROUP_NAME,ATOM_GROUP_DESC,IS_USERD,IMAGE_URL,BOOKMARK_ID,CREATE_TIME,USER_ID) values (?,?,?,'1','',?,CAST(date as DATE FORMAT 'YYYY-MM-DD') || ' ' || time,'')";

	private static final String SQL_DELBYID = "delete from "
			+ Constant.SEG_BIZ_GROUP_DEF + " where ATOM_GROUP_ID = ? ";

	private static final String SQL_MODBYID = "update "
			+ Constant.SEG_BIZ_GROUP_DEF
			+ " set ATOM_GROUP_NAME=?,ATOM_GROUP_DESC=?, BOOKMARK_ID=? where ATOM_GROUP_ID = ? ";

	private Connection conn = null;

	public SEG_BIZ_GROUP_DEFDAO(Connection con) {

		this.conn = con;

	}

	public List findById(int id) {
		List list = new ArrayList();
		if (null == instlist)
			findAll();
		for (int i = 0; i < instlist.size(); i++)
			if (((SEG_BIZ_GROUP_DEFDTO) instlist.get(i)).getBOOKMARK_ID() == id)
				list.add(instlist.get(i));
		/*
		 * PreparedStatement ps = null; ResultSet rs = null; List list = new
		 * ArrayList(); try { ps = conn.prepareStatement(SQL_FINDBYID);
		 * ps.setInt(1, id); rs = ps.executeQuery(); while (rs.next()) {
		 * SEG_BIZ_GROUP_DEFDTO dto = new SEG_BIZ_GROUP_DEFDTO();
		 * dto.setATOM_GROUP_ID(rs.getInt(1));
		 * dto.setATOM_GROUP_NAME(rs.getString(2));
		 * dto.setATOM_GROUP_DESC(rs.getString(3));
		 * dto.setIS_USERD(rs.getString(4)); dto.setIMAGE_URL(rs.getString(5));
		 * dto.setBOOKMARK_ID(rs.getInt(6));
		 * dto.setCREATE_TIME(rs.getTimestamp(7));
		 * dto.setUSER_ID(rs.getString(8)); list.add(dto); } } catch
		 * (SQLException e) { // TODO Auto-generated catch block
		 * e.printStackTrace(); } finally { try { if (rs != null) {
		 * 
		 * rs.close(); } if (ps != null) { ps.close(); } } catch (SQLException
		 * e) { // TODO Auto-generated catch block e.printStackTrace(); } }
		 */
		return list;
	}

	public SEG_BIZ_GROUP_DEFDTO findId(int id) {
		if (null == instlist)
			findAll();
		for (int i = 0; i < instlist.size(); i++)
			if (((SEG_BIZ_GROUP_DEFDTO) instlist.get(i)).getATOM_GROUP_ID() == id)
				return (SEG_BIZ_GROUP_DEFDTO) instlist.get(i);
		/*
		 * PreparedStatement ps = null; ResultSet rs = null;
		 * SEG_BIZ_GROUP_DEFDTO dto = new SEG_BIZ_GROUP_DEFDTO(); try { ps =
		 * conn.prepareStatement(SQL_FINDID); ps.setInt(1, id); rs =
		 * ps.executeQuery(); if (rs.next()) {
		 * 
		 * dto.setATOM_GROUP_ID(rs.getInt(1));
		 * dto.setATOM_GROUP_NAME(rs.getString(2));
		 * dto.setATOM_GROUP_DESC(rs.getString(3));
		 * dto.setIS_USERD(rs.getString(4)); dto.setIMAGE_URL(rs.getString(5));
		 * dto.setBOOKMARK_ID(rs.getInt(6));
		 * dto.setCREATE_TIME(rs.getTimestamp(7));
		 * dto.setUSER_ID(rs.getString(8)); } } catch (SQLException e) { // TODO
		 * Auto-generated catch block e.printStackTrace(); } finally { try { if
		 * (rs != null) {
		 * 
		 * rs.close(); } if (ps != null) { ps.close(); } } catch (SQLException
		 * e) { // TODO Auto-generated catch block e.printStackTrace(); } }
		 */
		return null;
	}

	public List findAll() {
		if (instlist != null)
			return instlist;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List list = new ArrayList();
		try {
			ps = conn.prepareStatement(SQL_FINDALL);
			rs = ps.executeQuery();
			while (rs.next()) {
				SEG_BIZ_GROUP_DEFDTO dto = new SEG_BIZ_GROUP_DEFDTO();
				dto.setATOM_GROUP_ID(rs.getInt(1));
				dto.setATOM_GROUP_NAME(rs.getString(2));
				dto.setATOM_GROUP_DESC(rs.getString(3));
				dto.setIS_USERD(rs.getString(4));
				dto.setIMAGE_URL(rs.getString(5));
				dto.setBOOKMARK_ID(rs.getInt(6));
				dto.setCREATE_TIME(rs.getTimestamp(7));
				dto.setUSER_ID(rs.getString(8));
				list.add(dto);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {

					rs.close();
				}
				if (ps != null) {
					ps.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		instlist = list;
		return list;
	}

	public List findAllByKind(String kindId) {
		if (null == instlist)
			findAll();
		List list = new ArrayList();
		if (kindId.equals("1")) {
			for (int i = 0; i < instlist.size(); i++) {
				if (((SEG_BIZ_GROUP_DEFDTO) instlist.get(i)).getBOOKMARK_ID() < 8)
					list.add((SEG_BIZ_GROUP_DEFDTO) instlist.get(i));
			}
		}else if(kindId.equals("2")) {
			for (int i = 0; i < instlist.size(); i++) {
				if (((SEG_BIZ_GROUP_DEFDTO) instlist.get(i)).getBOOKMARK_ID() > 7)
					list.add((SEG_BIZ_GROUP_DEFDTO) instlist.get(i));
			}
		}
		return list;
	}

	public void insert(ModifyatomForm modifyatomForm) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(SQL_INSERT);
			ps.setInt(1, Integer.parseInt(modifyatomForm.getBIZ_ID()));
			ps.setString(2, modifyatomForm.getName());
			ps.setString(3, modifyatomForm.getBIZ_DESC());
			ps.setInt(4, Integer.parseInt(modifyatomForm.getPrename()));
			ps.executeUpdate();
			instlist = null;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {

					rs.close();
				}
				if (ps != null) {
					ps.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();

			}
		}

	}

	public void deleteById(String[] id) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(SQL_DELBYID);
			for (int i = 0; i < id.length; i++) {
				ps.setInt(1, Integer.parseInt(id[i]));
				ps.executeUpdate();
			}
			instlist = null;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {

					rs.close();
				}
				if (ps != null) {
					ps.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();

			}
		}

	}

	public void modifyById(String id, ModifyatomForm modifyatomForm) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(SQL_MODBYID);
			ps.setString(1, modifyatomForm.getName());
			ps.setString(2, modifyatomForm.getBIZ_DESC());
			ps.setInt(3, Integer.parseInt(modifyatomForm.getPrename()));
			ps.setInt(4, Integer.parseInt(id));
			ps.executeUpdate();
			instlist = null;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {

					rs.close();
				}
				if (ps != null) {
					ps.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();

			}
		}

	}
}
